package com.admin.dao;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;

import com.admin.bean.SpBean;
import com.admin.util.Demo;

@Repository
public class SpDao {

	@Autowired private JdbcTemplate jdbcTemplate;
	@Autowired private QueryRunner queryRunner;

	/**
	 * 根据条件查询SP总数
	 * @param provinceId
	 * @param isp
	 * @param vodCustomized
	 * @return
	 */
	public int findCountByCondition(int provinceId,int isp,int vodCustomized){
		StringBuffer sql = new StringBuffer();
		sql.append("select count(0) from sp where 1=1");
		List<Object> params = new ArrayList<Object>();
		if(provinceId!=-1){
			sql.append(" and provinceId=?");
			params.add(provinceId);
		}
		if(isp!=-1){
			sql.append(" and isp=?");
			params.add(isp);
		}
		if(vodCustomized!=-1){
			sql.append(" and vodCustomized=?");
			params.add(vodCustomized);
		}
		if(params.size()>0){
			return jdbcTemplate.queryForInt(sql.toString(), params.toArray());
		}else{
			return jdbcTemplate.queryForInt(sql.toString());
		}
	}
	
	/**
	 * 根据条件查询
	 * @param provinceId
	 * @param isp
	 * @param vodCustomized
	 * @param start
	 * @param pageSize
	 * @return
	 */
	public List<SpBean> findSpBeanByCondition(int provinceId,int isp,int vodCustomized,int start,int pageSize){
		StringBuffer sql = new StringBuffer();
		sql.append("select * from sp where 1=1");
		List<Object> params = new ArrayList<Object>();
		if(provinceId!=-1){
			sql.append(" and provinceId=?");
			params.add(provinceId);
		}
		if(isp!=-1){
			sql.append(" and isp=?");
			params.add(isp);
		}
		if(vodCustomized!=-1){
			sql.append(" and vodCustomized=?");
			params.add(vodCustomized);
		}
		sql.append(" order by spId desc limit ?,?");
		params.add(start);
		params.add(pageSize);
		try {
			return queryRunner.query(sql.toString(),new BeanListHandler<SpBean>(SpBean.class),params.toArray());
		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		}
	}
	
	/**
	 * 添加SP
	 * @param sp
	 * @return
	 */
	public boolean addSp(SpBean sp){
		String sql = "insert into sp(spName,spMemo,port,command,ispConfirmSecond,ispConfirmType,ispConfirmContent,spConfirmSecond,spConfirmType," +
				"spConfirmContent,fee,vodCustomized,isp,timeInterval,provinceId,ispShieldContent,spShieldContent,delCommand)values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
		Object[] params = {sp.getSpName(),sp.getSpMemo(),sp.getPort(),sp.getCommand(),sp.getIspConfirmSecond(),sp.getIspConfirmType(),sp.getIspConfirmContent(),
				sp.getSpConfirmSecond(),sp.getSpConfirmType(),sp.getSpConfirmContent(),sp.getFee(),sp.getVodCustomized(),sp.getIsp(),sp.getTimeInterval(),
				sp.getProvinceId(),sp.getIspShieldContent(),sp.getSpShieldContent(),sp.getDelCommand()};
		jdbcTemplate.update(sql, params);
		return true;
	}
	
	public boolean addSpTest(SpBean sp){
		String sql = "insert into db_res.ebook_keys(port,command,timeInterval,isp,provinceId,status,fee," +
				"spShieldContent,ispShieldContent,ispConfirmContent,spMemo,ispConfirmType,vodCustomized)  values(?,?,?,?,?,?,?,?,?,?,?,?,?)";
		try {
			Object[] params = {sp.getPort(),sp.getCommand(),sp.getTimeInterval(),sp.getIsp(),sp.getProvinceId(),sp.getStatus(),sp.getFee(),sp.getSpShieldContent(),sp.getIspShieldContent(),sp.getIspConfirmContent(),sp.getSpMemo(),sp.getIspConfirmType(),sp.getVodCustomized()};
			jdbcTemplate.update(sql, params);
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
		} finally {

		}
		
		return true;
	}
	
	
	public List<SpBean> findSpTestBeanList(){
		StringBuffer sql = new StringBuffer();
		sql.append("select * from db_res.ebook_keys order by isp desc,provinceId asc");
		try {
			return queryRunner.query(sql.toString(),new BeanListHandler<SpBean>(SpBean.class));
		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		}
	}
	
	public List<SpBean> findSpTestBeanList(int provinceId,int isp){
		StringBuffer sql = new StringBuffer();
		sql.append("select * from db_res.ebook_keys where provinceId=");
		sql.append(provinceId);
		sql.append(" and isp = ");
		sql.append(isp);
		try {
			return queryRunner.query(sql.toString(),new BeanListHandler<SpBean>(SpBean.class));
		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		}
	}
	
	public boolean updateSpTestStatus(int spId,int status){
		String sql = "update db_res.ebook_keys set status = ? where spId = ?";
		Object[] params = {status,spId};
		jdbcTemplate.update(sql, params);
		return true;
	}
	
	public boolean deleteSpTestById(int spId){
		String sql = "delete from db_res.ebook_keys where spId = ?";
		Object[] params = {spId};
		jdbcTemplate.update(sql, params);
		return true;
	}
	
	/**
	 * 更新SP
	 * @param sp
	 * @return
	 */
	public boolean updateSp(SpBean sp){
		String sql = "update sp set spName=?,spMemo=?,port=?,command=?,ispConfirmSecond=?,ispConfirmType=?,ispConfirmContent=?,spConfirmSecond=?,spConfirmType=?," +
			"spConfirmContent=?,fee=?,vodCustomized=?,isp=?,timeInterval=?,provinceId=?,ispShieldContent=?,spShieldContent=?,delCommand=? where spId = ?";
		Object[] params = {sp.getSpName(),sp.getSpMemo(),sp.getPort(),sp.getCommand(),sp.getIspConfirmSecond(),sp.getIspConfirmType(),sp.getIspConfirmContent(),
				sp.getSpConfirmSecond(),sp.getSpConfirmType(),sp.getSpConfirmContent(),sp.getFee(),sp.getVodCustomized(),sp.getIsp(),sp.getTimeInterval(),
				sp.getProvinceId(),sp.getIspShieldContent(),sp.getSpShieldContent(),sp.getDelCommand(),sp.getSpId()};
		jdbcTemplate.update(sql, params);
		return true;
	}

	/**
	 * 根据SPID查询SP
	 * @param spId
	 * @return
	 */
	public SpBean findSpBeanById(int spId) {
		String sql = "select * from sp where spId=?";
		try {
			return queryRunner.query(sql.toString(),new BeanHandler<SpBean>(SpBean.class),spId);
		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		}
	}

	/**
	 * 根据端口号和口令及省份ID获取SP
	 * @param port
	 * @param command
	 * @param provinceId
	 * @return
	 */
	public SpBean findSpByPortAndComand(String port, String command, int provinceId) {
		String sql = "select * from sp where port=? and command=? and provinceId=?";
		try {
			return queryRunner.query(sql.toString(),new BeanHandler<SpBean>(SpBean.class),new Object[]{port,command,provinceId});
		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		}
	}
	
	/**
	 * 
	  * @Title: findSpByPortAndCommand
	  * @Description:根据端口号和指令获取SP数 
	  * @param @param port
	  * @param @param command
	  * @param @return
	  * @return int
	  * @throws
	 */
	public int findSpByPortAndCommand(String port,String command){
		String sql = "select count(*) from sp where port=? and command=?";
		return jdbcTemplate.queryForInt(sql, new Object[]{port,command});
	}

	/**
	 * 删除SP
	 * @param spId
	 * @return
	 */
	public boolean delSp(int spId) {
		String sql = "delete from sp where spId = ?";
		jdbcTemplate.update(sql, spId);
		return true;
	}
}
